package sqlinjection;

import core.User;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Formatter;
import java.util.List;

/**
 *
 * @author Atuan
 */
public class DBHandler {
    private static SecureRandom random = new SecureRandom();

    private static String nextSalt()
    {
        //generate string with 12 length (5*12=60) - 5 is number of bytes of BigInteger i assume.
        return new BigInteger(60, random).toString(32);
    }
    
    private static String generateHash(String pass, String salt){
        System.out.println("SALT: "+salt);
        String result = pass + salt;
        try{
            MessageDigest crypt = MessageDigest.getInstance("SHA-1");
            crypt.reset();
            crypt.update(result.getBytes("UTF-8"));
            result = byteToHex(crypt.digest());
        } catch(Exception e){
            System.out.println("MD5 problem "+e.getMessage());
        }
        
        return result;
    }

    private static String byteToHex(final byte[] hash)
    {
        Formatter formatter = new Formatter();
        for (byte b : hash)
        {
            formatter.format("%02x", b);
        }
        String result = formatter.toString();
        formatter.close();
        return result;
    }
    
    public static Boolean addMovie(Movie movie){
        Connection con = null;
        Boolean result=false;
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            try (PreparedStatement sta = con.prepareStatement("insert into movies(id,title,releaseyear,contributorId,genre) values(?,?,?,?,?)")) {
                sta.setLong(1, movie.getId());
                sta.setString(2, movie.getTitle());
                sta.setInt(3, movie.getReleaseYear());
                sta.setString(4, movie.getContributor());
                sta.setString(5, movie.getGenre());
                sta.executeUpdate();
                sta.close();
            }
            con.close();
            result = true;
            System.out.println("No error has been made.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
            return result;
        }
    }
    
    public static Boolean addMovieVulnerable(Movie movie){
        Connection con = null;
        Boolean result=false;
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            String query = "insert into movies(id,title,releaseyear,contributorId,genre) values("
                    + Long.toString(movie.getId())+",'"+movie.getTitle()+"',"+Integer.toString(movie.getReleaseYear())+
                    ",'"+movie.getContributor()+"','"+movie.getGenre()+"')";
            Statement sta = con.createStatement();
                sta.execute(query);
                sta.close();
            con.close();
            result = true;
            
            System.out.println("No error has been made.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
            return result;
        }
    }
    
    public static List<Movie> getAllMovies(){
        List<Movie> result = new ArrayList<>();
        
        Connection con = null;
        try{
            System.out.println("Before connectedAll");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connectedAll");
            try (PreparedStatement sta = con.prepareStatement("select * from movies")) {
                ResultSet rs = sta.executeQuery();
                while(rs.next()){
                    Movie movie = new Movie(rs.getInt("ID"),rs.getString("TITLE"),
                            rs.getInt("RELEASEYEAR"),rs.getString("GENRE"),rs.getString("contributor"));
                    result.add(movie);
                }
                rs.close();
                sta.close();
            }
            con.close();
            
            System.out.println("No error has been made.All");
            System.out.println("All "+result.size());
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
        
        return result;
    }
    
    public static void editMovie(Movie movie, User user){
        Connection con = null;
        List<Movie> result = new ArrayList<>();
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            PreparedStatement sta = con.prepareStatement("update movies SET title=?,genre=?, releaseyear=? WHERE id=? and contributor=?");
            sta.setString(1, movie.getTitle());
            sta.setString(2, movie.getGenre());
            sta.setInt(3, movie.getReleaseYear());
            sta.setLong(4, movie.getId());
            sta.setString(5, user.getLogin());
            sta.executeUpdate();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getMoviebytitle.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
    }
    
    public static void editMovieNotSafely(Movie movie, User user){
        Connection con = null;
        List<Movie> result = new ArrayList<>();
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            String query = "update movies SET title='"+movie.getTitle()+"',genre='"+movie.getGenre()+"', releaseyear="+
                    movie.getReleaseYear()+" WHERE id="+movie.getId()+" and contributor='"+user.getLogin()+"'";
            Statement sta = con.createStatement();
            System.out.println("QUERY= "+query);
            sta.executeUpdate(query);
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getMoviebytitle.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
    }
    
    public static List<Movie> getMovieByTitle2(String title){
        Connection con = null;
        List<Movie> result = new ArrayList<>();
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            PreparedStatement sta = con.prepareStatement("select * from movies where title LIKE ?");
            sta.setString(1, "%"+title+"%");
            ResultSet rs = sta.executeQuery();
            while(rs.next()){
                result.add(new Movie(rs.getInt("ID"),rs.getString("TITLE"),
                            rs.getInt("RELEASEYEAR"),rs.getString("GENRE"),rs.getString("contributor")));
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getMoviebytitle.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
        return result;
    }
    
    public static Movie getMovieById(int id){
        Connection con = null;
        List<Movie> result = new ArrayList<>();
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            PreparedStatement sta = con.prepareStatement("select * from movies where id=?");
            sta.setInt(1, id);
            ResultSet rs = sta.executeQuery();
            while(rs.next()){
                result.add(new Movie(rs.getInt("ID"),rs.getString("TITLE"),
                            rs.getInt("RELEASEYEAR"),rs.getString("GENRE"),rs.getString("contributor")));
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getMoviebytitle.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
        Movie res = new Movie();
        if(!result.isEmpty()){
            res = result.get(0);
        }
        return res;
    }
    
    public static List<Movie> getMovieByTitle(String title){
        Connection con = null;
        List<Movie> result = new ArrayList<>();
        try{
            System.out.println("Before connected");
//            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            System.out.println("connected");
            String query = "select * from movies where title LIKE '%"+title+"%'";
            Statement sta = con.createStatement();
            System.out.println("QUERY= "+query);
            ResultSet rs = sta.executeQuery(query);
            while(rs.next()){
                result.add(new Movie(rs.getInt("ID"),rs.getString("TITLE"),
                            rs.getInt("RELEASEYEAR"),rs.getString("GENRE"),rs.getString("contributor")));
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getMoviebytitle.");
        } catch(Exception e){
            System.out.println("error during addmovie "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error");
                }
            }
        }
        return result;
    }
    
    public static Boolean login(String login, String password){
        Boolean res = false;
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            PreparedStatement sta = con.prepareStatement("select * from users where login=? AND password=?");
            sta.setString(1, login);
            sta.setString(2, password);
            ResultSet rs = sta.executeQuery();
            if(rs.next()){
                res = true;
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in login.");
        } catch(Exception e){
            System.out.println("error during login "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error "+e.getMessage());
                }
            }
        }        
        return res;
    }
    
    public static Boolean secureLogin(String login, String password){
        Boolean res = false;
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            PreparedStatement sta = con.prepareStatement("select * from users where login=?");
            sta.setString(1, login);
            ResultSet rs = sta.executeQuery();
            if(rs.next()){
                String passh = rs.getString("passwordh");
                String salt = rs.getString("salt");
//                String result = generateHash(password,nextSalt());
                String result = generateHash(password,salt);
//                System.out.println("DATA: "+result+" - "+salt);
                if(result.equals(passh)){
                    res = true;
                }
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in login.");
        } catch(Exception e){
            System.out.println("error during login "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("addMovie error "+e.getMessage());
                }
            }
        }        
        return res;
    }
    
    public static User getUserData(String login){
        User user = new User();
        user.setLogin(login);
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/movies;user=admin;password=admin1");
            PreparedStatement sta = con.prepareStatement("select * from users where login = ?");
            sta.setString(1, login);
            ResultSet rs = sta.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                int subs = rs.getInt("SUBSCRIBER");
                user.setId(id);
                user.setPassword(rs.getString("password"));
                user.setSubscriber(subs);
            }
            rs.close();
            sta.close();
            con.close();
            
            System.out.println("No error has been made in getuserdata.");
        } catch(Exception e){
            System.out.println("error during getuserdata "+ e.getMessage());
        } finally {
            if(con != null){
                try{
                    con.close();
                } catch(Exception e){
                    System.out.println("getuserdata error "+e.getMessage());
                }
            }
            return user;
        }        
    }
}
